*********************************************************************
*This file cleans schooling by employment status from the Census/ACS*
*********************************************************************

foreach year in 1990 2000 2008 {

use if year==`year' using "$raw_data_lmarket/ipums_census.dta", clear

*Drop institutional group quarters* 
quietly: drop if gqtyped>=100 & gqtyped<=499
*Drop alaska and hawai*
quietly: drop if statefip==2 | statefip==15

keep if age>=19&age<=64

keep cntygp* puma statefip perwt multyear empstat classwkrd ind1990 age educd bpl school relate sploc pernum race hispan sex serial hispan race wkswork2 uhrswork

*weeks worked
gen wkswork=0
replace wkswork=7 if wkswork2==1 
replace wkswork=20 if wkswork2==2 
replace wkswork=33 if wkswork2==3 
replace wkswork=43.5 if wkswork2==4 
replace wkswork=48.5 if wkswork2==5 
replace wkswork=51 if wkswork2==6

gen wt_year = wkswork*uhrswork

*employment
gen ipums_pop = 1
gen ipums_emp = (empstat==1)
gen ipums_emp_ft = (empstat==1 & wt_year>=1560) //where a full-time worker works about 130 hours a month (ca. 30 hours a week) and thus 1560 hours a year
gen ipums_emp_pt = (empstat==1 & wt_year<1560 & wt_year>0) //less than 30 hours a week (130 a month)
gen ipums_emp_ps = (empstat==1 & wt_year<1040 & wt_year>0) //less than 20 hours a week (86.6 a month)

gen ipums_uemp = (empstat==2)
gen ipums_npart = (empstat==3)

*schooling
foreach var of varlist ipums_* {
	gen `var'_sc = (`var'==1 & school==2)
}

*education
foreach var of varlist ipums_* {
	gen `var'_l = (`var'==1 & educd>=2 & educd<=71)
	gen `var'_a = (`var'==1 & educd>=81 & educd<=83)
	gen `var'_b = (`var'==1 & educd>=100 & educd<=101)
	
}

*age
foreach var of varlist ipums_* {
	gen `var'_a19_34 = (`var'==1 & age>=19&age<=34)
	gen `var'_a35_64 = (`var'==1 & age>=35&age<=64)
}


keep cntygp* puma statefip ipums_* perwt multyear

**Merge czones using geography xwalk**
if `year'==1990{
gen puma1990=statefip*10000+puma
collapse (sum) ipums_* [fw=perwt], by(puma1990)  fast
count if puma1990!=.
joinby puma1990 using "$project/xwalks/xwalks_geography/puma1990_czone.dta", unmatched(master)
assert czone!=. 
}
else if `year'==2000  | `year'==2008 {
replace puma=1801 if puma==77777
gen puma2000=statefip*10000+puma
collapse (sum) ipums_* [fw=perwt], by(puma2000)  fast
count if puma2000!=.
joinby puma2000 using "$project/xwalks/xwalks_geography/puma2000_czone.dta", unmatched(master)
assert czone!=. 
}
**Aggregate at the czone level**
collapse (sum) ipums_* [iw=afac], by(czone) fast
foreach var of varlist ipums_*{
rename `var' `var'_`year'
}
save "$clean_data_lmarket/czone`year'_schooling_employment.dta", replace
}

